###########################################################################
####################### SQL queries used for creating fixed tables on DISC
####################### Debtor table
####################### Creditor table
####################### Instrument table
####################### Orbis table

## create AnaCredit tables

import pyodbc

## instrument table
# omit only the ones that have the sum of all 3 amounts as NULL or 0
sql_conn = pyodbc.connect('DSN=DISC DP Impala 64bit',autocommit=True)

query = """

CREATE TABLE lab_org_dgr_fir.ac_instr_201912_KK
AS
 SELECT DISTINCT t1.dt_rfrnc,
                 t2.cntry as cntry_dbtr,
                 t1.obsrvd_agnt_id,
                 t1.dbtr_id, 
                 t1.crdtr_id,
                 t1.srvcr_id,
                 t1.orgntr_id,  
                 t1.cntrct_id,
                 t1.instrmnt_id,
                 t1.dt_incptn,
                 t1.dt_sttlmnt,
                 t1.typ_instrmnt,
                 CASE WHEN t1.typ_instrmnt = 20 OR t1.typ_instrmnt = 51 OR t1.typ_instrmnt = 1001 THEN 'Revolving credit'
                     WHEN t1.typ_instrmnt = -99 then 'Technicall null'
                     WHEN t1.typ_instrmnt = 1000 then 'Deposits'
                     WHEN t1.typ_instrmnt = 1003 then 'Reverse repurchase agreements'
                     WHEN t1.typ_instrmnt = 1004 then 'Loans'
                     WHEN t1.typ_instrmnt = 1002 then 'Credit lines'
                     WHEN t1.typ_instrmnt = 71 then 'Trade receivables'
                     WHEN t1.typ_instrmnt = 80 then 'Finance leases'
                     ELSE NULL END AS typ_instr_corr,                                                         
                 t1.prjct_fnnc_ln,
                 CASE WHEN t1.prjct_fnnc_ln = -99 THEN 'Technicall null'
                      WHEN t1.prjct_fnnc_ln = 1 THEN 'Project finance loan'
                      WHEN t1.prjct_fnnc_ln = 1 THEN 'Non-project finance loan'
                      ELSE NULL END AS prjct_fnnc_ln_nm,
                 t1.prps,
                 CASE WHEN t1.prps = -99 THEN 'Technical null' 
                      WHEN t1.prps = 11 THEN 'Other purposes'
                      WHEN t1.prps = 12 THEN 'Residential real estate purchase'
                      WHEN t1.prps = 13 THEN 'Commercial real estate purchase'
                      WHEN t1.prps = 4 THEN 'Margin lending'
                      WHEN t1.prps = 5 THEN 'Debt financing'
                      WHEN t1.prps = 6 THEN 'Imports'
                      WHEN t1.prps = 7 THEN 'Exports'
                      WHEN t1.prps = 8 THEN 'Construction investment'
                      WHEN t1.prps = 9 THEN 'Working capital facility'  
                      ELSE NULL END AS prps_nm,
                 t1.dflt_stts_instrmnt,
                 CASE WHEN t1.dflt_stts_instrmnt = -99 THEN 'Technical null'
                      WHEN t1.dflt_stts_instrmnt = 14 THEN 'Not in default'
                      WHEN t1.dflt_stts_instrmnt = 18 THEN 'Delault because both unlikely to pay and more than 90/180 past due'
                      WHEN t1.dflt_stts_instrmnt = 19 THEN 'Delault because unlikely to pay'
                      WHEN t1.dflt_stts_instrmnt = 20 THEN 'Delault because more than 90/180 past due'
                      ELSE NULL END AS dflt_stts_instrmnt_nm,
                 t1.dflt_stts_dbtr,
                 CASE WHEN t1.dflt_stts_dbtr = -99 THEN 'Technical null'
                      WHEN t1.dflt_stts_dbtr = 14 THEN 'Not in default'
                      WHEN t1.dflt_stts_dbtr = 18 THEN 'Delault because both unlikely to pay and more than 90/180 past due'
                      WHEN t1.dflt_stts_dbtr = 19 THEN 'Delault because unlikely to pay'
                      WHEN t1.dflt_stts_dbtr = 20 THEN 'Delault because more than 90/180 past due'
                      ELSE NULL END AS dflt_stts_dbtr_nm,
                 CASE WHEN t1.dflt_stts_instrmnt IN (18,19,20) THEN 1   
                      WHEN t1.dflt_stts_instrmnt IN (14) THEN 0 
                      ELSE t1.dflt_stts_instrmnt END AS dflt_stts_instr_flag,
                 CASE WHEN t1.dflt_stts_dbtr IN (18,19,20) THEN 1
                      WHEN t1.dflt_stts_dbtr IN (14) THEN 0 
                      ELSE t1.dflt_stts_dbtr END AS dflt_stts_debtor_flag,
                 t1.pd_dbtr,
                 t1.is_scrd,
                 t1.typ_scrtstn,
                 CASE WHEN t1.typ_scrtstn = -99 THEN 'Technical null'
                      WHEN t1.typ_scrtstn = 1 THEN 'Traditional securitization'
                      WHEN t1.typ_scrtstn = 6 THEN 'Synthetic securitization'
                      WHEN t1.typ_scrtstn = 7 THEN 'Not securitised'
                      ELSE NULL END AS typ_scrtsn_nm,
                 t1.nmbr_prtctn,
                 t1.nmbr_dbtr,
                 t1.otstndng_nmnl_amnt_cv,
                 CASE WHEN t1.otstndng_nmnl_amnt_cv <= 0 OR t1.otstndng_nmnl_amnt_cv >= 300000000 THEN NULL ELSE otstndng_nmnl_amnt_cv END AS otstndng_nmnl_amnt_cv_c,
                 t1.off_blnc_sht_amnt_cv,
                 CASE WHEN t1.off_blnc_sht_amnt_cv < 0 THEN NULL ELSE off_blnc_sht_amnt_cv END AS off_blnc_sht_amnt_cv_c,
                 t1.cmmtmnt_incptn_cv,
                 CASE WHEN t1.cmmtmnt_incptn_cv <= 0 THEN NULL ELSE cmmtmnt_incptn_cv END AS cmmtmnt_incptn_cv_c,
                 t1.prtctn_allctd_vl_inst_cv,
                 CASE WHEN t1.prtctn_allctd_vl_inst_cv < 0 THEN NULL ELSE prtctn_allctd_vl_inst_cv END AS prtctn_allctd_vl_inst_cv_c,
                 t1.trnsfrrd_amnt_instrmnt,
                 t1.annlsd_agrd_rt,
                 t1.orgnl_mtrty,
                 CASE WHEN t1.orgnl_mtrty <= 0 THEN NULL ELSE orgnl_mtrty END AS orgnl_mtrty_c,
                 t1.rsdl_mtrty,
                 CASE WHEN t1.rsdl_mtrty < 0 THEN NULL ELSE rsdl_mtrty END AS rsdl_mtrty_c
                 
 FROM
        crp_anacredit.anacredit_dm_instrmnt_fct_cc t1
 INNER JOIN
        (SELECT DISTINCT entty_riad_id, dt_rfrnc, cntry
        FROM crp_anacredit.anacredit_dm_d_dbtr_cc cc
        WHERE instttnl_sctr = 'S11' AND
              SUBSTR(ecnmc_actvty,1,2) NOT IN ('64','65','66') AND
              cntry in ('AT','BE','CY','DE','EE','ES','FI','FR','GR','IE','IT','LT','LU','LV','MT','NL','PT','SI','SK') AND
              dt_rfrnc = 201912 AND
              entty_riad_cd = entty_riad_cd_le) t2
 ON t1.dbtr_id = t2.entty_riad_id AND t1.dt_rfrnc = t2.dt_rfrnc

 WHERE t1.crrncy_dnmntn = 'EUR' AND
       t1.typ_instrmnt NOT IN (1000, 1003, -99) AND  
       t1.nmbr_crdtr = 1 AND
       t1.syndctd_cntrct_id IS NULL

                """
                
sql_conn.execute(query)               
sql_conn.close()


## debtor table
sql_conn = pyodbc.connect('DSN=DISC DP Impala 64bit',autocommit=True)

query =  """
 
CREATE TABLE lab_org_dgr_fir.ac_dbtr_201912_KK
AS
 SELECT tt.*, 

CASE WHEN tt.nmbr_emplys_le_flag = 1 
        THEN (CASE WHEN (tt.blnc_sht_ttl_le_flag = 1 OR tt.annl_trnvr_le_flag = 1)
                                                                                  
                                                                                  THEN (
                                                                                           CASE WHEN tt.nmbr_emplys_le_c < 10 AND (tt.blnc_sht_ttl_le_c <= 2000000 OR tt.annl_trnvr_le_c <= 2000000) THEN 4
                                                                                                WHEN tt.nmbr_emplys_le_c < 50 AND (tt.blnc_sht_ttl_le_c <= 10000000 OR tt.annl_trnvr_le_c <= 10000000) THEN 3
                                                                                                WHEN tt.nmbr_emplys_le_c < 250 AND (tt.blnc_sht_ttl_le_c <= 43000000 OR tt.annl_trnvr_le_c <= 50000000) THEN 2
                                                                                                ELSE 1 END
                                                                                         ) 
                
                WHEN tt.blnc_sht_ttl_le_flag = 0 AND 
                     tt.annl_trnvr_le_flag = 0         THEN (
                                                               CASE WHEN tt.nmbr_emplys_le_c < 10 THEN 4
                                                                    WHEN tt.nmbr_emplys_le_c >= 10 AND tt.nmbr_emplys_le_c < 50 THEN 3
                                                                    WHEN tt.nmbr_emplys_le_c >= 50 AND tt.nmbr_emplys_le_c < 250 THEN 2
                                                                    ELSE 1 END  
                                                             )
                ELSE NULL END)
     
     
     WHEN tt.nmbr_emplys_le_flag = 0 AND
         tt.blnc_sht_ttl_le_flag = 1 AND 
          tt.annl_trnvr_le_flag = 0          THEN (CASE WHEN tt.blnc_sht_ttl_le_c <= 2000000 THEN 4
                                                         WHEN tt.blnc_sht_ttl_le_c <= 10000000 THEN 3
                                                         WHEN tt.blnc_sht_ttl_le_c <= 43000000 THEN 2
                                                         ELSE 1 END) 
      
      
      WHEN tt.nmbr_emplys_le_flag = 0 AND
         tt.blnc_sht_ttl_le_flag = 0 AND 
          tt.annl_trnvr_le_flag = 1          THEN (CASE WHEN tt.annl_trnvr_le_c <= 2000000 THEN 4
                                                         WHEN tt.annl_trnvr_le_c <= 10000000 THEN 3
                                                         WHEN tt.annl_trnvr_le_c <= 50000000 THEN 2
                                                         ELSE 1 END)
      
      
      WHEN tt.nmbr_emplys_le_flag = 0 AND
         tt.blnc_sht_ttl_le_flag = 1 AND 
          tt.annl_trnvr_le_flag = 1          THEN (CASE WHEN ((tt.blnc_sht_ttl_le_c - tt.annl_trnvr_le_c) >= 0 AND tt.blnc_sht_ttl_le_c <= 2000000) OR ((tt.blnc_sht_ttl_le_c - tt.annl_trnvr_le_c) < 0 AND tt.annl_trnvr_le_c <= 2000000) THEN 4
                                                         WHEN ((tt.blnc_sht_ttl_le_c - tt.annl_trnvr_le_c) >= 0 AND tt.blnc_sht_ttl_le_c <= 10000000) OR ((tt.blnc_sht_ttl_le_c - tt.annl_trnvr_le_c) < 0 AND tt.annl_trnvr_le_c <= 10000000) THEN 3
                                                         WHEN ((tt.blnc_sht_ttl_le_c - tt.annl_trnvr_le_c) >= 0 AND tt.blnc_sht_ttl_le_c <= 43000000) OR ((tt.blnc_sht_ttl_le_c - tt.annl_trnvr_le_c) < 0 AND tt.annl_trnvr_le_c <= 50000000) THEN 2
                                                         ELSE 1 END)             
      
      
      ELSE -99 END AS entrprs_sz_le_corr,
      
CASE   WHEN SUBSTR(ecnmc_actvty,1,2) IN ('01','02','03') THEN 'A'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('05','06','07','08','09') THEN 'B'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('10','11','12','13','14','15','16',
                                           '17','18','19','20', '21','22','23',
                                           '24','25','26','27','28','29','30',
                                           '31','32','33') THEN 'C'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('35') THEN 'D'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('36','37','38','39') THEN 'E'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('41','42','43') THEN 'F'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('45','46','47') THEN 'G'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('49','50','51','52','53') THEN 'H'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('55','56') THEN 'I'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('58','59','60','61','62','63') THEN 'J'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('64','65','66') THEN 'K'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('68') THEN 'L'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('69','70','71','72','73','74','75') THEN 'M'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('77','78','79','80','81','82') THEN 'N'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('84') THEN 'O'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('85') THEN 'P'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('86','87','88') THEN 'Q'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('90','91','92','93') THEN 'R'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('94','95','96') THEN 'S'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('97','98') THEN 'T'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('99') THEN 'U'
       ELSE NULL END AS nace_code          
 
 
 FROM (
        SELECT DISTINCT 
                                     d.*,
                                     CASE WHEN entrprs_sz_le > 0 THEN 1 ELSE 0 END AS entrprs_sz_le_flag,
                                     CASE WHEN nmbr_emplys_le_c >= 1 THEN 1 ELSE 0 END AS nmbr_emplys_le_flag,
                                     CASE WHEN (blnc_sht_ttl_le_c > 0) OR (blnc_sht_ttl_le_c < 0) THEN 1 ELSE 0 END AS blnc_sht_ttl_le_flag,                                      
                                     CASE WHEN annl_trnvr_le_c > 0 THEN 1 ELSE 0 END AS annl_trnvr_le_flag                                           
                     
                     FROM (
                                 SELECT  cntry,
                                         entty_riad_id,
                                         entty_riad_cd,
                                         lei,
                                         trrtrl_unt,
                                         pstl_cd,                                             
                                         ecnmc_actvty,
                                         entrprs_sz_le,
                                         CASE WHEN entrprs_sz_le = -99 THEN 'Technical null'
                                              WHEN entrprs_sz_le = 1 THEN 'Large'
                                              WHEN entrprs_sz_le = 2 THEN 'Medium'
                                              WHEN entrprs_sz_le = 3 THEN 'Small'
                                              WHEN entrprs_sz_le = 4 THEN 'Micro'
                                         ELSE NULL END AS sz_nm,
                                         nmbr_emplys_le,
                                         blnc_sht_ttl_le,
                                         annl_trnvr_le,
                                         CASE WHEN nmbr_emplys_le >= 5000000 THEN NULL ELSE nmbr_emplys_le END AS nmbr_emplys_le_c,
                                         CASE WHEN blnc_sht_ttl_le >= 628969628628969 THEN NULL ELSE blnc_sht_ttl_le END AS blnc_sht_ttl_le_c,
                                         CASE WHEN annl_trnvr_le >= 5500000000000000 THEN NULL ELSE annl_trnvr_le END AS annl_trnvr_le_c
                                 
                                 FROM
                                        crp_anacredit.anacredit_dm_d_dbtr_cc t1  
                                 WHERE   
                                        instttnl_sctr = 'S11' AND 
                                        SUBSTR(ecnmc_actvty,1,2) NOT IN ('64','65','66') AND
                                        cntry in ( 'AT','BE','CY','DE','EE','ES','FI','FR','GR','IE','IT','LT','LU','LV','MT','NL','PT','SI','SK') AND
                                        dt_rfrnc = 201912 AND
                                        entty_riad_cd = entty_riad_cd_le
                        ) d
 
 )  tt
      
      """        

sql_conn.execute(query)               
sql_conn.close()


## creditor table
sql_conn = pyodbc.connect('DSN=DISC DP Impala 64bit',autocommit=True)

query =  """
 
CREATE TABLE lab_org_dgr_fir.ac_crdtr_201912_KK
AS
SELECT tt.*, 

    CASE WHEN tt.nmbr_emplys_le_flag = 1 
            THEN (CASE WHEN (tt.blnc_sht_ttl_le_flag = 1 OR tt.annl_trnvr_le_flag = 1)
                                                                                      
                                                                                      THEN (
                                                                                               CASE WHEN tt.nmbr_emplys_le < 10 AND (tt.blnc_sht_ttl_le <= 2000000 OR tt.annl_trnvr_le <= 2000000) THEN 4
                                                                                                    WHEN tt.nmbr_emplys_le < 50 AND (tt.blnc_sht_ttl_le <= 10000000 OR tt.annl_trnvr_le <= 10000000) THEN 3
                                                                                                    WHEN tt.nmbr_emplys_le < 250 AND (tt.blnc_sht_ttl_le <= 43000000 OR tt.annl_trnvr_le <= 50000000) THEN 2
                                                                                                    ELSE 1 END
                                                                                             ) 
                    
                    WHEN tt.blnc_sht_ttl_le_flag = 0 AND 
                         tt.annl_trnvr_le_flag = 0         THEN (
                                                                   CASE WHEN tt.nmbr_emplys_le < 10 THEN 4
                                                                        WHEN tt.nmbr_emplys_le >= 10 AND tt.nmbr_emplys_le < 50 THEN 3
                                                                        WHEN tt.nmbr_emplys_le >= 50 AND tt.nmbr_emplys_le < 250 THEN 2
                                                                        ELSE 1 END  
                                                                 )
                    ELSE NULL END)
         
         
         WHEN tt.nmbr_emplys_le_flag = 0 AND
             tt.blnc_sht_ttl_le_flag = 1 AND 
              tt.annl_trnvr_le_flag = 0          THEN (CASE WHEN tt.blnc_sht_ttl_le <= 2000000 THEN 4
                                                             WHEN tt.blnc_sht_ttl_le <= 10000000 THEN 3
                                                             WHEN tt.blnc_sht_ttl_le <= 43000000 THEN 2
                                                             ELSE 1 END) 
          
          
          WHEN tt.nmbr_emplys_le_flag = 0 AND
             tt.blnc_sht_ttl_le_flag = 0 AND 
              tt.annl_trnvr_le_flag = 1          THEN (CASE WHEN tt.annl_trnvr_le <= 2000000 THEN 4
                                                             WHEN tt.annl_trnvr_le <= 10000000 THEN 3
                                                             WHEN tt.annl_trnvr_le <= 50000000 THEN 2
                                                             ELSE 1 END)
          
          
          WHEN tt.nmbr_emplys_le_flag = 0 AND
             tt.blnc_sht_ttl_le_flag = 1 AND 
              tt.annl_trnvr_le_flag = 1          THEN (CASE WHEN ((tt.blnc_sht_ttl_le - tt.annl_trnvr_le) >= 0 AND tt.blnc_sht_ttl_le <= 2000000) OR ((tt.blnc_sht_ttl_le - tt.annl_trnvr_le) < 0 AND tt.annl_trnvr_le <= 2000000) THEN 4
                                                             WHEN ((tt.blnc_sht_ttl_le - tt.annl_trnvr_le) >= 0 AND tt.blnc_sht_ttl_le <= 10000000) OR ((tt.blnc_sht_ttl_le - tt.annl_trnvr_le) < 0 AND tt.annl_trnvr_le <= 10000000) THEN 3
                                                             WHEN ((tt.blnc_sht_ttl_le - tt.annl_trnvr_le) >= 0 AND tt.blnc_sht_ttl_le <= 43000000) OR ((tt.blnc_sht_ttl_le - tt.annl_trnvr_le) < 0 AND tt.annl_trnvr_le <= 50000000) THEN 2
                                                             ELSE 1 END)             
          
          
          ELSE -99 END AS entrprs_sz_le_corr,
          
CASE   WHEN SUBSTR(ecnmc_actvty,1,2) IN ('01','02','03') THEN 'A'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('05','06','07','08','09') THEN 'B'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('10','11','12','13','14','15','16',
                                           '17','18','19','20', '21','22','23',
                                           '24','25','26','27','28','29','30',
                                           '31','32','33') THEN 'C'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('35') THEN 'D'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('36','37','38','39') THEN 'E'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('41','42','43') THEN 'F'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('45','46','47') THEN 'G'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('49','50','51','52','53') THEN 'H'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('55','56') THEN 'I'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('58','59','60','61','62','63') THEN 'J'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('64','65','66') THEN 'K'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('68') THEN 'L'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('69','70','71','72','73','74','75') THEN 'M'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('77','78','79','80','81','82') THEN 'N'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('84') THEN 'O'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('85') THEN 'P'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('86','87','88') THEN 'Q'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('90','91','92','93') THEN 'R'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('94','95','96') THEN 'S'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('97','98') THEN 'T'
       WHEN SUBSTR(ecnmc_actvty,1,2) IN ('99') THEN 'U'
       ELSE NULL END AS nace_code          
 
 
 FROM (
            SELECT DISTINCT 
                                         d.*,
                                         CASE WHEN entrprs_sz_le > 0 THEN 1 ELSE 0 END AS entrprs_sz_le_flag,
                                         CASE WHEN nmbr_emplys_le > 0 THEN 1 ELSE 0 END AS nmbr_emplys_le_flag,
                                         CASE WHEN (blnc_sht_ttl_le > 0) OR (blnc_sht_ttl_le < 0) THEN 1 ELSE 0 END AS blnc_sht_ttl_le_flag,                                      
                                         CASE WHEN annl_trnvr_le > 0 THEN 1 ELSE 0 END AS annl_trnvr_le_flag                                           
                         
                         FROM (
                                     SELECT  cntry,
                                             cntry_le,
                                             entty_riad_id,
                                             entty_riad_cd,
                                             entty_riad_cd_le,
                                             ultmt_prnt_undrt_id,
                                             immdt_prnt_undrt_id,
                                             lei,
                                             trrtrl_unt,
                                             trrtrl_unt_le,
                                             pstl_cd, 
                                             pstl_cd_le,
                                             ecnmc_actvty,
                                             entrprs_sz_le,
                                         CASE WHEN entrprs_sz_le = -99 THEN 'Technical null'
                                              WHEN entrprs_sz_le = 1 THEN 'Large'
                                              WHEN entrprs_sz_le = 2 THEN 'Medium'
                                              WHEN entrprs_sz_le = 3 THEN 'Small'
                                              WHEN entrprs_sz_le = 4 THEN 'Micro'
                                              ELSE NULL END AS sz_nm,
                                             nmbr_emplys_le,
                                             blnc_sht_ttl_le,
                                             annl_trnvr_le,
                                             is_dmstc_actvty,
                                             is_ea_actvty
                                     
                                     FROM
                                            crp_anacredit.anacredit_dm_d_crdtr_cc t1

                                     WHERE   
                                            dt_rfrnc = 201912 
                                            
                                ) d
 
 )  tt
          
      """         

sql_conn.execute(query)               
sql_conn.close()


## Orbis table
# new Orbis table with all variables
sql_conn = pyodbc.connect('DSN=DISC DP Impala 64bit',autocommit=True)

query =  """
 
CREATE TABLE lab_org_dgr_fir.orbis2_dt_201912_kk
AS
select      
                t2.entty_riad_cd,
                substr(t1.bvdidnumber,1,2) as cntry,
                t1.bvdidnumber,
                filingtype, 
                case when filingtype in ('Annual report') then 1 else 0 end as filing_flag,
                max(case when filingtype in ('Annual report') then 1 else 0 end) over (partition by t2.entty_riad_cd) as filingtype_pref,
                t1.consolidationcode,
                case when consolidationcode = 'U1' then 1
                     when consolidationcode = 'U2' then 2
                     when consolidationcode = 'C1' then 3
                     else NULL end as cons_flag,
                min(case when consolidationcode = 'U1' then 1
                     when consolidationcode = 'U2' then 2
                     when consolidationcode = 'C1' then 3
                     else NULL end) over (partition by t2.entty_riad_cd) as cons_flag_min,
                max(case when consolidationcode = 'U1' then 1
                     when consolidationcode = 'U2' then 2
                     when consolidationcode = 'C1' then 3
                     else NULL end) over (partition by t2.entty_riad_cd) as cons_flag_max,                
                closingdate,
                max(cast(closingdate as int)) over (partition by t2.entty_riad_cd, substr(consolidationcode, 1, 1)) as max_date,
                auditstatus,
                case when auditstatus = 'Unqualified' then 1 
                     when auditstatus = 'Qualified' then 2
                     when auditstatus = 'No opinion' then 3
                     when auditstatus = 'Unaudited' then 4
                     when auditstatus = 'Audit n.a.' then 5
                     when auditstatus IS NULL then 6
                     when auditstatus = 'Self-disclosed' then 7
                     else 8 end as audit_flag,
                min(case when auditstatus = 'Unqualified' then 1 
                     when auditstatus = 'Qualified' then 2
                     when auditstatus = 'No opinion' then 3
                     when auditstatus = 'Unaudited' then 4
                     when auditstatus = 'Audit n.a.' then 5
                     when auditstatus IS NULL then 6
                     when auditstatus = 'Self-disclosed' then 7
                     else 8 end) over (partition by t2.entty_riad_cd, filingtype, consolidationcode, closingdate) as audit_flag_min,
                max(case when auditstatus = 'Unqualified' then 1 
                     when auditstatus = 'Qualified' then 2
                     when auditstatus = 'No opinion' then 3
                     when auditstatus = 'Unaudited' then 4
                     when auditstatus = 'Audit n.a.' then 5
                     when auditstatus IS NULL then 6
                     when auditstatus = 'Self-disclosed' then 7
                     else 8 end) over (partition by t2.entty_riad_cd, filingtype, consolidationcode, closingdate) as audit_flag_max,                
                accountingpractice,
                case when accountingpractice = 'Local GAAP' then 1
                     when accountingpractice = 'IFRS' then 2
                     else 3 end as accounting_flag,
                min(case when accountingpractice = 'Local GAAP' then 1
                         when accountingpractice = 'IFRS' then 2
                         else 3 end) over (partition by t2.entty_riad_cd, filingtype, consolidationcode, closingdate, auditstatus) as accounting_flag_min,               
                max(case when accountingpractice = 'Local GAAP' then 1
                         when accountingpractice = 'IFRS' then 2
                         else 3 end) over (partition by t2.entty_riad_cd, filingtype, consolidationcode, closingdate, auditstatus) as accounting_flag_max,
                numberofemployees, 
                totalassets, 
                operatingrevenueturnover, 
                CASE WHEN numberofemployees > 0 THEN 1 ELSE 0 END AS nmbr_empl_orb_flag,
                CASE WHEN totalassets > 0 THEN 1 ELSE 0 END AS totalassets_orb_flag,
                CASE WHEN operatingrevenueturnover > 0 THEN 1 ELSE 0 END AS turnover_orb_flag,
                loans, 
                longtermdebt,
                roausingnetincome,
                intangiblefixedassets,
                tangiblefixedassets,
                otherfixedassets,
                fixedassets,
                currentassets ,
                debtors ,
                cashcashequivalent,
                researchdevelopmentexpenses,        
                liquidityratiox,
                ebitdamargin,
                case when nvl(t1.totalassets,0) = 0 then NULL
                     else (nvl(loans,0) + nvl(longtermdebt,0))/nvl(t1.totalassets,0) 
                     end as leverage
               
from crp_orbis.orbis_industry_global_financials_and_ratios_eur t1
inner join lab_prj_lab_riad.riad_orbis_mppng_d_1 t2
on t1.bvdidnumber = t2.bvd_id
where t1.consolidationcode in ('U1', 'U2', 'C1') and
      cast(t1.closingdate as integer) <= 20190630 and
      substr(t1.bvdidnumber,1,2) in ('AT','BE','CY','DE','EE','ES','FI','FR','GR','IE','IT','LT','LU','LV','MT','NL','PT','SI','SK') and
      t1.numberofmonths = '12'
          
"""

sql_conn.execute(query)               
sql_conn.close()


      